﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO = Microsoft.MSE.Catalog.Contracts;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

namespace Microsoft.MSE.Catalog.Module.Data
{
    public class Capability
    {
        public Capability()
        { }

        public static Guid CreateCapability(DTO.Capability capability, SqlTransaction txn, SqlConnection conn)
        {
            Guid ID;
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[CreateCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter OwnerNameParam = new SqlParameter("@OwnerName", SqlDbType.NText);
                    SqlParameter OwnerContactParam = new SqlParameter("@OwnerContact", SqlDbType.NChar, 10);
                    SqlParameter TxValueParam = new SqlParameter("@TxValue", SqlDbType.Money);
                    SqlParameter ExCostParam = new SqlParameter("@ExCost", SqlDbType.Money);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Direction = ParameterDirection.Output;
                    NameParam.Value = capability.Name;
                    if (capability.OwnerName != null)
                    {
                        OwnerNameParam.Value = capability.OwnerName;
                    }
                    else
                    {
                        OwnerNameParam.Value = DBNull.Value;
                    }
                    if (capability.OwnerContact != null)
                    {
                        OwnerContactParam.Value = capability.OwnerContact;
                    }
                    else
                    {
                        OwnerContactParam.Value = DBNull.Value;
                    }
                    if (capability.Description != null)
                    {
                        DescriptionParam.Value = capability.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }
                    if (capability.TxValue != null)
                    {
                        TxValueParam.Value = capability.TxValue;
                    }
                    else
                    {
                        TxValueParam.Value = DBNull.Value;
                    }
                    if (capability.ExCost != null)
                    {
                        ExCostParam.Value = capability.ExCost;
                    }
                    else
                    {
                        ExCostParam.Value = DBNull.Value;
                    }

                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(OwnerNameParam);
                    cmd.Parameters.Add(OwnerContactParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(TxValueParam);
                    cmd.Parameters.Add(ExCostParam);


                    cmd.ExecuteNonQuery();
                    ID = (Guid)IDParam.Value;
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return ID;
        }

        public static DTO.Capability UpdateCapability(DTO.Capability capability, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[UpdateCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);
                    SqlParameter NameParam = new SqlParameter("@Name", SqlDbType.NVarChar, 400);
                    SqlParameter OwnerNameParam = new SqlParameter("@OwnerName", SqlDbType.NVarChar, 200);
                    SqlParameter OwnerContactParam = new SqlParameter("@OwnerContact", SqlDbType.NChar, 200);
                    SqlParameter DescriptionParam = new SqlParameter("@Description", SqlDbType.NText);
                    SqlParameter TxValueParam = new SqlParameter("@TxValue", SqlDbType.Money);
                    SqlParameter ExCostParam = new SqlParameter("@ExCost", SqlDbType.Money);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = capability.ID;
                    NameParam.Value = capability.Name;
                    if (capability.OwnerName != null)
                    {
                        OwnerNameParam.Value = capability.OwnerName;
                    }
                    else
                    {
                        OwnerNameParam.Value = DBNull.Value;
                    }
                    if (capability.OwnerContact != null)
                    {
                        OwnerContactParam.Value = capability.OwnerContact;
                    }
                    else
                    {
                        OwnerContactParam.Value = DBNull.Value;
                    }
                    if (capability.Description != null)
                    {
                        DescriptionParam.Value = capability.Description;
                    }
                    else
                    {
                        DescriptionParam.Value = DBNull.Value;
                    }
                    if (capability.TxValue != null)
                    {
                        TxValueParam.Value = capability.TxValue;
                    }
                    else
                    {
                        TxValueParam.Value = DBNull.Value;
                    }
                    if (capability.ExCost != null)
                    {
                        ExCostParam.Value = capability.ExCost;
                    }
                    else
                    {
                        ExCostParam.Value = DBNull.Value;
                    }

                    //


                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(NameParam);
                    cmd.Parameters.Add(OwnerNameParam);
                    cmd.Parameters.Add(OwnerContactParam);
                    cmd.Parameters.Add(DescriptionParam);
                    cmd.Parameters.Add(TxValueParam);
                    cmd.Parameters.Add(ExCostParam);

                    cmd.ExecuteNonQuery();
                }
            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return capability;
        }

        public static void DeleteCapability(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[DeleteCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static DTO.Capability GetCapability(Guid ID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.Capability capability = new DTO.Capability();
            const string sprocname = "[ServiceEngine].[GetCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@ID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = ID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            reader.Read();
                            capability.ID = reader.GetGuid(0);
                            capability.Name = reader.GetString(1);

                            if (reader.IsDBNull(2))
                            {
                                capability.OwnerName = null;
                            }
                            else
                            {
                                capability.OwnerName = reader.GetString(2);
                            }
                            
                            if (reader.IsDBNull(3))
                            {
                                capability.OwnerContact = null;
                            }
                            else
                            {
                                capability.OwnerContact = reader.GetString(3);
                            }
                            if (reader.IsDBNull(4))
                            {
                                capability.TxValue = 0;
                            }
                            else
                            {
                                capability.TxValue = reader.GetDecimal(4);
                            }
                            if (reader.IsDBNull(5))
                            {
                                capability.ExCost = 0;
                            }
                            else
                            {
                                capability.ExCost = reader.GetDecimal(5);
                            }

                            if (reader.IsDBNull(6))
                            {
                                capability.Description = null;
                            }
                            else
                            {
                                capability.Description = reader.GetString(6);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }


            return capability;

        }

        public static DTO.CapabilityList ListCapabilities(SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.CapabilityList list = new DTO.CapabilityList();
            const string sprocname = "[ServiceEngine].[ListCapabilities]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    cmd.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Capabilities = new List<DTO.CapabilityListElement>();
                            while (reader.Read())
                            {
                                DTO.CapabilityListElement element = new DTO.CapabilityListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Capabilities.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.MSETypeList ListTypesForCapability(Guid capabilityID, Boolean OutputFlag, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.MSETypeList list = new DTO.MSETypeList();
            const string sprocname = "[ServiceEngine].[ListTypesForCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter OutputFlagParam = new SqlParameter("@OutputFlag", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = capabilityID;
                    if (OutputFlag)
                        OutputFlagParam.Value = 1;
                    else
                        OutputFlagParam.Value = 0;
                    cmd.Parameters.Add(IDParam);
                    cmd.Parameters.Add(OutputFlagParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.MSETypes = new List<DTO.MSETypeListElement>();
                            while (reader.Read())
                            {
                                DTO.MSETypeListElement element = new DTO.MSETypeListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.MSETypes.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static DTO.OperationList ListOperationsForCapability(Guid capabilityID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            DTO.OperationList list = new DTO.OperationList();
            const string sprocname = "[ServiceEngine].[ListOperationsForCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = capabilityID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list.Operations = new List<DTO.OperationListElement>();
                            while (reader.Read())
                            {
                                DTO.OperationListElement element = new DTO.OperationListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }
                                list.Operations.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static List<DTO.CapabilityListElement> ListCapabilitiesByGroup(Guid entityGroupID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            List<DTO.CapabilityListElement> list = new List<DTO.CapabilityListElement>();
            const string sprocname = "[ServiceEngine].[ListEntitiesByGroup]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter IDParam = new SqlParameter("@entityGroupID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    IDParam.Value = entityGroupID;
                    cmd.Parameters.Add(IDParam);

                    using (SqlDataReader reader = cmd.ExecuteReader())
                    {
                        if (reader.HasRows)
                        {
                            list = new List<DTO.CapabilityListElement>();
                            while (reader.Read())
                            {
                                DTO.CapabilityListElement element = new DTO.CapabilityListElement();
                                element.ID = reader.GetGuid(0);
                                element.Name = reader.GetString(1);
                                if (reader.IsDBNull(2))
                                {
                                    element.Description = null;
                                }
                                else
                                {
                                    element.Description = reader.GetString(2);
                                }

                                list.Add(element);
                            }
                        }
                    }
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

            return list;

        }

        public static void AssignTypeToCapability(Guid capabilityID, Guid typeID, Boolean OutputFlag, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignTypeToCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter CapabilityIDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter TypeIDParam = new SqlParameter("@TypeID", SqlDbType.UniqueIdentifier);
                    SqlParameter OutputFlagParam = new SqlParameter("@OutputFlag", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    CapabilityIDParam.Value = capabilityID;
                    TypeIDParam.Value = typeID;
                    if (OutputFlag)
                        OutputFlagParam.Value = 1;
                    else
                        OutputFlagParam.Value = 0;

                    cmd.Parameters.Add(CapabilityIDParam);                    
                    cmd.Parameters.Add(TypeIDParam);
                    cmd.Parameters.Add(OutputFlagParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemoveTypeFromCapability(Guid capabilityID, Guid typeID, Boolean outputFlag, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveTypeFromCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter CapabilityIDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter TypeIDParam = new SqlParameter("@TypeID", SqlDbType.UniqueIdentifier);
                    SqlParameter OutputFlagParam = new SqlParameter("@OutputFlag", SqlDbType.Bit);

                    cmd.CommandType = CommandType.StoredProcedure;
                    CapabilityIDParam.Value = capabilityID;
                    TypeIDParam.Value = typeID;
                    if (outputFlag)
                        OutputFlagParam.Value = 1;
                    else
                        OutputFlagParam.Value = 0;

                    cmd.Parameters.Add(CapabilityIDParam);
                    cmd.Parameters.Add(TypeIDParam);
                    cmd.Parameters.Add(OutputFlagParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static void AssignOperationToCapability(Guid capabilityID, Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignOperationToCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter CapabilityIDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    CapabilityIDParam.Value = capabilityID;
                    OperationIDParam.Value = operationID;

                    cmd.Parameters.Add(CapabilityIDParam);
                    cmd.Parameters.Add(OperationIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemoveOperationFromCapability(Guid capabilityID, Guid operationID, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveOperationFromCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter CapabilityIDParam = new SqlParameter("@CapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter OperationIDParam = new SqlParameter("@OperationID", SqlDbType.UniqueIdentifier);

                    cmd.CommandType = CommandType.StoredProcedure;
                    CapabilityIDParam.Value = capabilityID;
                    OperationIDParam.Value = operationID;

                    cmd.Parameters.Add(CapabilityIDParam);
                    cmd.Parameters.Add(OperationIDParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }

        public static void AssignCapabilityToCapability(Guid parentCapabilityID, Guid childCapabilityID, Int16 order, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[AssignCapabilityToCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter parentCapabilityIDParam = new SqlParameter("@ChildCapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter childCapabilityIDParam = new SqlParameter("@ParentCapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter orderParam = new SqlParameter("@Order", SqlDbType.TinyInt);

                    cmd.CommandType = CommandType.StoredProcedure;
                    parentCapabilityIDParam.Value = parentCapabilityID;
                    childCapabilityIDParam.Value = childCapabilityID;
                    orderParam.Value = order;

                    cmd.Parameters.Add(parentCapabilityIDParam);
                    cmd.Parameters.Add(childCapabilityIDParam);
                    cmd.Parameters.Add(orderParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }
        }

        public static void RemoveCapabilityFromCapability(Guid parentCapabilityID, Guid childCapabilityID, Int16 order, SqlTransaction txn, SqlConnection conn)
        {
            bool autoclose = false;
            const string sprocname = "[ServiceEngine].[RemoveCapabilityFromCapability]";

            try
            {
                if (conn == null) conn = CatalogSvcHelper.GetConnection();
                if (conn.State == ConnectionState.Closed)
                {
                    autoclose = true;
                    conn.Open();
                }

                using (SqlCommand cmd = new SqlCommand(sprocname, conn, txn))
                {
                    SqlParameter parentCapabilityIDParam = new SqlParameter("@ChildCapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter childCapabilityIDParam = new SqlParameter("@ParentCapabilityID", SqlDbType.UniqueIdentifier);
                    SqlParameter orderParam = new SqlParameter("@Order", SqlDbType.TinyInt);

                    cmd.CommandType = CommandType.StoredProcedure;
                    parentCapabilityIDParam.Value = parentCapabilityID;
                    childCapabilityIDParam.Value = childCapabilityID;
                    orderParam.Value = order;

                    cmd.Parameters.Add(parentCapabilityIDParam);
                    cmd.Parameters.Add(childCapabilityIDParam);
                    cmd.Parameters.Add(orderParam);

                    cmd.ExecuteNonQuery();
                }

            }
            finally
            {
                if (autoclose && (conn != null)) conn.Close(); //Close connection if opened in this method
            }

        }


    }
}
